package com.ccsoft.annoreflect.utils;

import com.ccsoft.annoreflect.OrderPolicy;
import com.ccsoft.annoreflect.anno.Table;
import com.ccsoft.annoreflect.anno.TableField;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

/**
 * @author chanchaw
 * @create 2022-09-23 7:55
 */
public class SQLProvider<T> {

    private static final String PLACEHOLDER_TABLENAME = "[TABLENAME]";
    private static final String PLACEHOLDER_PKFIELD = "[PKFIELD]";
    private static final String PLACEHOLDER_PKVALUE = "[PKVALUE]";

    public String getQuerySQL(T record) throws InvocationTargetException, IllegalAccessException {
        if(record == null) throw new RuntimeException("可以是空白对象，但不可传入NULL！");

        Class<?> clazz = record.getClass();
        Table tableAnno = clazz.getAnnotation(Table.class);
        if(tableAnno == null) throw new RuntimeException("没有给" + clazz + "使用注解@Table(tableName = \"billtype\")");
        String tableName = tableAnno.tableName();
        if(tableName == null || tableName.length()<=0) throw new RuntimeException("没有为注解@Table设置表名！");

        String orderSQL = getOrderBySQL(tableAnno);

        StringBuffer sb = new StringBuffer("SELECT * FROM " + tableName + " WHERE 1=1");
        if(record == null) return sb.append(orderSQL).toString();

        Field[] fields = clazz.getDeclaredFields();
        if(fields == null || fields.length<=0) return sb.append(orderSQL).toString();


        for(Field field:fields){
            Object val = null;
            try {
                val = getFieldValue(record,field);
            } catch (NoSuchMethodException e) {
                continue;
            }
            if(val == null) continue;
            String valString = fieldValue2String(field,val);
            sb.append(" AND " + field.getName() + "=" + valString);
        }

        return sb.append(orderSQL).toString();
    }

    private String getOrderBySQL(Table tableAnno){
        String orderBy = tableAnno.orderBy();
        OrderPolicy orderPolicy = tableAnno.orderPolicy();

        if(orderBy == null || orderBy.length()<=0) return "";
        if(orderPolicy == null) return " ORDER BY " + orderBy + " ASC";
        return " ORDER BY " + orderBy + " " + orderPolicy.getDesc();
    }

    public String getInsertSQL(){
        return null;
    }

    public String getUpdateSQL(T record,Boolean selective) throws InvocationTargetException, IllegalAccessException {
        StringBuffer sb = new StringBuffer("update " + getTableName(record) + " set ");

        // 拼接中间的 set
        Class<?> clazz = record.getClass();
        Field[] declaredFields = clazz.getDeclaredFields();
        if(null == declaredFields || declaredFields.length<=0) throw new RuntimeException("类"+ clazz + "没有属性！");
        for(Field field:declaredFields){
            // 如果是主键则跳出并继续下一次循环
            TableField fieldAnnotation = field.getAnnotation(TableField.class);
            if( null == fieldAnnotation) continue;
            Boolean isPK = fieldAnnotation.isPK();
            if(isPK == true) continue;// 主键字段不修改

            // 属性 serialVersionUID 没有 getter、setter
            // 所以出现异常 NoSuchMethodException 要跳过
            Object val = null;
            try {
                val = getFieldValue(record,field);
            }catch (NoSuchMethodException e){
                continue;
            }

            if(selective == true && val == null) continue;
            sb.append(field.getName() + "=" + fieldValue2String(field,val) + ",");
        }
        sb.delete(sb.length()-1,sb.length());

        // 没有=表示没有设置更新的字段，为非法SQL
        int i = sb.indexOf("=");
        if(i == -1) throw new RuntimeException("没有设置需要更新的字段！");

        // 拼接 where
        sb.append(getWherePKValue(record));
        return sb.toString();
    }

    // 全量更新
    public String getUpdateSQLAll(T record) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        return getUpdateSQL(record,false);
    }

    // 选择性更新 - value = null 的字段不会被更新
    public String getUpdateSQLSelective(T record) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        return getUpdateSQL(record,true);
    }

    public String getDeleteSQL(T record) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String sql = "delete from " + getTableName(record);
        sql = sql + getWherePKValue(record);
        return sql;
    }

    private String getTableName(T record){
        Class<?> clazz = record.getClass();
        final Table tableAnno = clazz.getAnnotation(Table.class);// 获取类头上的指定类型的注解
        if( null == tableAnno ) throw new RuntimeException("没有为" + clazz + "使用 Table 注解！");

        final String tableName = tableAnno.tableName();// 获取表名
        if(null == tableName || StringUtils.isEmpty(tableName)) throw new RuntimeException("没有为" + clazz + "使用注解@Table(tableName = \"billtype\")设置表名！");
        return tableName;
    }

    // 返回类似 where id = 11 的 where 子句
    private String getWherePKValue(T record) throws InvocationTargetException, IllegalAccessException {
        String sql = " where " + PLACEHOLDER_PKFIELD + " = " + PLACEHOLDER_PKVALUE;

        Class<?> clazz = record.getClass();
        final Table tableAnno = clazz.getAnnotation(Table.class);// 获取类头上的指定类型的注解
        if( null == tableAnno ) throw new RuntimeException("没有为" + clazz + "使用 Table 注解！");

        final String tableName = tableAnno.tableName();// 获取表名
        if(null == tableName || StringUtils.isEmpty(tableName)) throw new RuntimeException("没有为" + clazz + "使用注解@Table(tableName = \"billtype\")设置表名！");

        Field[] declaredFields = clazz.getDeclaredFields();
        boolean flagFirstPK = true;

        if(null == declaredFields || declaredFields.length<=0) throw new RuntimeException("类" + clazz + "没有设置属性！");
        for (Field field:declaredFields){
            TableField fieldAnno = field.getAnnotation(TableField.class);
            if(null == fieldAnno) continue;

            String fieldName = fieldAnno.fieldName();
            Boolean isPK = fieldAnno.isPK();

            if(isPK == true){
                // 检测主键字段占位符应该存在，否则表示实体类中设置了多个属性为主键字段
                // 此时要报错，无法构建删除的SQL
                if(flagFirstPK == false){
                    int i = sql.indexOf(PLACEHOLDER_PKFIELD);
                    if( i !=-1) throw new RuntimeException("实体类"+clazz+"中设置了多个主键字段，请保证只有一个属性为主键字段后再执行本方法！");
                }

                flagFirstPK = false;
                sql = sql.replace(PLACEHOLDER_PKFIELD,fieldName);

                // 获取属性的 getter 方法，并获取其 value
                Object fieldValue = null;
                try {
                    fieldValue = getFieldValue(record,field);
                }catch (NoSuchMethodException e){
                    continue;
                }


                if(null == fieldValue) return "";// 主键值为空则无法删除

                String decorateFieldValue = fieldValue2String(field,fieldValue);
                sql = sql.replace(PLACEHOLDER_PKVALUE, decorateFieldValue);
            }
        }

        // 如果循环结束没有找到主键则报错，无法构建删除SQL
        int i = sql.indexOf(PLACEHOLDER_PKFIELD);
        if( i != -1) throw new RuntimeException("实体类"+clazz+"中没有设置主键字段，请使用注解 @TableField(isPK = true) 标识一个属性为主键字段后再执行本方法！");

        int j = sql.indexOf(PLACEHOLDER_PKVALUE);
        if( j != -1) throw new RuntimeException("传入的实体类"+clazz+"的对象中主键属性的value为null，无法创建删除SQL！");
        return sql;
    }

    private Object getFieldValue(T record,Field field) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        Class<?> clazz = record.getClass();
        String getterMethodName = "get" + field.getName().substring(0,1).toUpperCase()+field.getName().substring(1);
        Method getterMethod = clazz.getMethod(getterMethodName);
        return getterMethod.invoke(record);
    }

    private String fieldValue2String(Field field,Object val){
        if(val == null) return "null";

        if(field.getType() == String.class)
            return "'" +val+ "'";
        if(field.getType() == Integer.class)
            return val.toString();

        return null;
    }
}
